﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace BusinessLogic
{
    public abstract class BaseCommand<T> where T : class
    {
        protected SqlConnection conn;
        protected SqlCommand cmd;
        protected String Query;
        bool isReady;

        public T Result { get; set; }

        public void OpenConnection()
        {
            string connStr = ConfigurationManager.ConnectionStrings["TMTConnectionString"].ToString();
            conn = new SqlConnection(connStr);

            if (conn.State == ConnectionState.Open)
                conn.Close();
            conn.Open();

            isReady = false;
        }

        public void SetQuery(string s)
        {
            Query = s;

            OpenConnection();
            cmd = conn.CreateCommand();
            cmd.CommandText = Query;

            isReady = true;
        }

        public void Execute()
        {
            try
            {
                if (!isReady)
                    throw new Exception();

                //OpenConnection();
                //cmd = conn.CreateCommand();
                //cmd.CommandText = Query;

                Command();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public void ExecuteWithTransaction()
        {
            if (!isReady)
                throw new Exception();

            using (SqlTransaction sqlTran = conn.BeginTransaction())
            {
                OpenConnection();
                cmd = conn.CreateCommand();
                cmd.CommandText = Query;
                cmd.Transaction = sqlTran;

                try
                {
                    Command();
                }
                catch (Exception ex)
                {
                    try
                    {
                        // Attempt to roll back the transaction.
                        sqlTran.Rollback();
                    }
                    catch (Exception exRollback)
                    {
                        // Throws an InvalidOperationException if the connection  
                        // is closed or the transaction has already been rolled  
                        // back on the server.
                        throw exRollback;
                    }
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }

                sqlTran.Commit();
            }

        }

        public abstract void Command();

        public string GetDateStringFromDate(DateTime? dt)
        {
            if (dt != null)
            {
                return string.Format("'{0}-{1}-{2}'", dt.Value.Year, dt.Value.Month, dt.Value.Day); //เอา yyyy-mm-dd อ่ะ
            }
            else
                return "NULL";
        }

        public DateTime? GetNullableDateTime(SqlDataReader dr, int idx)
        {
            if (!dr.IsDBNull(idx))
                return dr.GetDateTime(idx);
            else
                return null;
        }

        public string GetNullableString(SqlDataReader dr, int idx)
        {
            if (!dr.IsDBNull(idx))
                return dr.GetString(idx);
            else
                return "";
        }

        public float? GetNullableFloat(SqlDataReader dr, int idx)
        {
            if (!dr.IsDBNull(idx))
                return dr.GetFloat(idx);
            else
                return null;
        }

        public decimal? GetNullableDecimal(SqlDataReader dr, int idx)
        {
            if (!dr.IsDBNull(idx))
                return dr.GetDecimal(idx);
            else
                return null;
        }

        public double? GetNullableDouble(SqlDataReader dr, int idx)
        {
            if (!dr.IsDBNull(idx))
                return dr.GetDouble(idx);
            else
                return null;
        }

        public bool? GetNullableBoolean(SqlDataReader dr, int idx)
        {
            if (!dr.IsDBNull(idx))
                return dr.GetBoolean(idx);
            else
                return null;
        }

        public int GetNullableInt(SqlDataReader dr, int idx)
        {
            if (!dr.IsDBNull(idx))
                return dr.GetInt32(idx);
            else
                return 0;
        }
    }
}
